// mysql db drives
package drivers

import (
	"fmt"
	"math/rand"
	"strconv"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"

	"account/configs"
)

// query need rows.Close to release db ins
// exec will release automatic
var MysqlDb *gorm.DB // db pool instance
var MysqlDbErr error // db err instance

/**
需求分析:
  1. 根据配置选择实例化连接对象
  2. 如果是集群的话，那么实例化所有的节点
  3. 然后增删改查通过，不同操作调用不同的节点
  ----------------------------------------------------------------
  分析：
  1. 指定对象
  2. 指定操作
  ----------------------------------------------------------------
  查询 走 Where Find Or Not
  更新 走 Update Model
  创建 走 Create
  迁移 走 Master

*/
type MyDB struct {
	username         string
	password         string
	host             string
	port             string
	name             string
	loc              string
	parsetime        string
	maxOpenConns     string
	maxIdleConns     string
	maxLifetimeConns string
	dbDSN            string
	charset          string
}

func NewDB(username, password, host, port, name string) *MyDB {
	return &MyDB{username: username, password: password, host: host, port: port, name: name}
}

func (db *MyDB) GetDBUri() {
	db.dbDSN = fmt.Sprintf("%s:%s@tcp(%s:%s)/%s",
		db.username,
		db.password,
		db.host,
		db.port,
		db.name,
	)
}

// 拼接URi
func (db *MyDB) uriConcat(option, value string) {
	if strings.Contains(db.dbDSN, "?") {
		db.dbDSN += fmt.Sprintf("&%s=%s", option, value)
	} else {
		db.dbDSN += fmt.Sprintf("?%s=%s", option, value)
	}
}

// 设置数据的时间区域
func (db *MyDB) SetLocal(loc string) {
	db.loc = loc
	db.uriConcat("loc", loc)
}

// 设置最大的活跃连接数
func (db *MyDB) SetMaxOpenConns(maxOpenConns string) {
	db.maxOpenConns = maxOpenConns
}

// 设置连接超时时间
func (db *MyDB) SetMaxLifetimeConns(maxLifetime string) {
	db.maxLifetimeConns = maxLifetime
}

// 配合时间区域
func (db *MyDB) SetParsetTime(parseTime string) {
	db.parsetime = parseTime
	db.uriConcat("parseTime", parseTime)
}

// 设置数据库中的显示数据类型
func (db *MyDB) SetCharset(charset string) {
	db.charset = charset
	db.uriConcat("charset", charset)
}

// 忘了
func (db *MyDB) SetMaxIdleConns(maxIdleConns string) {
	db.maxIdleConns = maxIdleConns
}

func (db *MyDB) GetUri() string {
	return db.dbDSN
}

type DBNodes struct {
	nodes map[string]*MyDB
}

func NewDBNodes(length int) *DBNodes {
	return &DBNodes{
		nodes: make(map[string]*MyDB, length),
	}
}

func (node *DBNodes) add(name string, nodes *MyDB) {
	node.nodes[name] = nodes
}

/**
  一主多从的分布式
*/
type MysqlDB struct {
	Master *gorm.DB
	Slave  []*gorm.DB
}

func NewMysqlDB() *MysqlDB {
	return &MysqlDB{}
}

/**
  创建连接DB对象
*/
func (mysqldb *MysqlDB) Open(dBNodes *DBNodes) {
	for nodeName, node := range dBNodes.nodes {
		if strings.Compare(nodeName, "master") == 0 {
			mysqldb.Master = mysqldb.ConstructOpen("mysql", node.dbDSN)
			mysqldb.SetOptions(mysqldb.Master, node)
		} else {
			slave := mysqldb.ConstructOpen("mysql", node.dbDSN)
			mysqldb.SetOptions(slave, node)
			mysqldb.Slave = append(mysqldb.Slave, slave)
			// 深坑
			slave.SingularTable(true)
		}
	}
}

/**
  初始化分布式数据库的配置
*/
func (mysqldb *MysqlDB) SetOptions(db *gorm.DB, node *MyDB) {

	dbDriver := db.DB()

	dbMaxOpenConns, _ := strconv.Atoi(node.maxOpenConns)
	dbDriver.SetMaxOpenConns(dbMaxOpenConns)

	maxIdleConns, _ := strconv.Atoi(node.maxIdleConns)
	dbDriver.SetMaxIdleConns(maxIdleConns)

	dbMaxLifetimeConns, _ := strconv.Atoi(node.maxLifetimeConns)
	dbDriver.SetConnMaxLifetime(time.Duration(dbMaxLifetimeConns))

	if MysqlDbErr = dbDriver.Ping(); nil != MysqlDbErr {
		panic("database connect failed: " + MysqlDbErr.Error())
	}
}

// 构建连接对象
func (mysqldb *MysqlDB) ConstructOpen(db, uri string) *gorm.DB {
	dbDriver, MysqlDbErr := gorm.Open(db, uri)
	if MysqlDbErr != nil {
		panic("database data source name error: " + MysqlDbErr.Error())
	}
	return dbDriver
}

func (mysqldb *MysqlDB) Create(values interface{}) *gorm.DB {
	fmt.Println("Master")
	return mysqldb.Master.Create(values)
}
func (mysqldb *MysqlDB) Delete(values interface{}) *gorm.DB {
	fmt.Println("Master")
	return mysqldb.Master.Delete(values)
}
func (mysqldb *MysqlDB) Model(values interface{}) *gorm.DB {
	fmt.Println("Master")
	return mysqldb.Master.Model(values)
}

func (mysqldb *MysqlDB) ModelSlave(values interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Model(values)
}

func (mysqldb *MysqlDB) Update(attrs ...interface{}) *gorm.DB {
	return mysqldb.Master.Update(attrs...)
}

// Omit specify fields that you want to ignore when saving to database for creating, updating
func (mysqldb *MysqlDB) Omit(columns ...string) *gorm.DB {
	return mysqldb.Master.Omit(columns...)
}

// 随机选择节点
func (mysqldb *MysqlDB) SlaveNode() *gorm.DB {
	s := rand.NewSource(time.Now().Unix())
	r := rand.New(s)
	num := r.Intn(len(mysqldb.Slave))
	slave := mysqldb.Slave[num]
	// fmt.Println("随机选取的节点", num, slave)
	return slave
}

// 获取第一条记录，按主键排序
// db.First(&user)
//SELECT * FROM users ORDER BY id LIMIT 1;
func (mysqldb *MysqlDB) First(out interface{}, where ...interface{}) *gorm.DB {
	fmt.Println("Slave")
	return mysqldb.SlaveNode().First(out, where...)
}

/**
  获取所有的记录
  db.Find(&users)
  SELECT * FROM users;

  获取模型记录数
  db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)
  SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users)
  SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)

  db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
  SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

  db.Table("deleted_users").Count(&count)
  SELECT count(*) FROM deleted_users;
*/
func (mysqldb *MysqlDB) Find(out interface{}, where ...interface{}) *gorm.DB {
	fmt.Println("Slave")
	return mysqldb.SlaveNode().Find(out, where...)
}

/**
  Struct
  db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
  SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;

  Map
  db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
  SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

  多主键 slice 查询
  db.Where([]int64{20, 21, 22}).Find(&users)
  SELECT * FROM users WHERE id IN (20, 21, 22);
  Or
  db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)

  Struct
  db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users)
  SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

  Map
  db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)
  SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2';

  使用 *gorm.expr 进行子查询
  db.Where("amount > ?", DB.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").QueryExpr()).Find(&orders)
  SELECT * FROM "orders"  WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders"  WHERE (state = 'paid')));

*/
func (mysqldb *MysqlDB) Where(query interface{}, args ...interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Where(query, args...)
}

/**
  和 Where查询类似
  db.Not("name", "jinzhu").First(&user)
  SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1;
*/
func (mysqldb *MysqlDB) Not(query interface{}, args ...interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Not(query, args...)
}

// 为查询 SQL 添加额外的选项
// db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10)
// SELECT * FROM users WHERE id = 10 FOR UPDATE;
func (mysqldb *MysqlDB) Set(name string, value interface{}) *gorm.DB {
	return mysqldb.SlaveNode().InstantSet(name, value)
}

/**
  未查询到
  db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user)
  SELECT * FROM USERS WHERE name = 'non_existing';
  user -> User{Name: "non_existing", Age: 20}

  db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user)
  SELECT * FROM USERS WHERE name = 'non_existing';
  user -> User{Name: "non_existing", Age: 20}

  查询到
  db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user)
  SELECT * FROM USERS WHERE name = jinzhu';
  user -> User{Id: 111, Name: "Jinzhu", Age: 20}
*/
func (mysqldb *MysqlDB) Attrs(attrs ...interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Attrs(attrs...)
}

/**
  未查询到
  db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)
  user -> User{Name: "non_existing", Age: 20}

  查询到
  db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user)
  SELECT * FROM USERS WHERE name = jinzhu';
  user -> User{Id: 111, Name: "Jinzhu", Age: 30}
*/
func (mysqldb *MysqlDB) Assign(attrs ...interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Assign(attrs...)
}

/**
  指定要从数据库检索的字段，默认情况下，将选择所有字段。

  db.Select("name, age").Find(&users)
  SELECT name, age FROM users;

  db.Select([]string{"name", "age"}).Find(&users)
  SELECT name, age FROM users;

  db.Table("users").Select("COALESCE(age,?)", 42).Rows()
  SELECT COALESCE(age,'42') FROM users;
*/
func (mysqldb *MysqlDB) Select(query interface{}, args ...interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Select(query, args...)
}

/**
  使用 Order 从数据库查询记录时，当第二个参数设置为 true 时，将会覆盖之前的定义条件。
  db.Order("age desc, name").Find(&users)
  SELECT * FROM users ORDER BY age desc, name;

  多个排序条件
  db.Order("age desc").Order("name").Find(&users)
  SELECT * FROM users ORDER BY age desc, name;

  重新排序
  db.Order("age desc").Find(&users1).Order("age", true).Find(&users2)
  SELECT * FROM users ORDER BY age desc; (users1)
  SELECT * FROM users ORDER BY age; (users2)
*/
func (mysqldb *MysqlDB) Order(value interface{}, reorder ...bool) *gorm.DB {
	return mysqldb.SlaveNode().Order(value, reorder...)
}

/**
  指定要查询的最大记录数
  db.Limit(3).Find(&users)
  SELECT * FROM users LIMIT 3;

  用 -1 取消 LIMIT 限制条件
  db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
  SELECT * FROM users LIMIT 10; (users1)
  SELECT * FROM users; (users2)
*/
func (mysqldb *MysqlDB) Limit(limit interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Limit(limit)
}

/**
  指定在开始返回记录之前要跳过的记录数。
  db.Offset(3).Find(&users)
  SELECT * FROM users OFFSET 3;

  用 -1 取消 OFFSET 限制条件
  db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
  SELECT * FROM users OFFSET 10; (users1)
  SELECT * FROM users; (users2)
*/
func (mysqldb *MysqlDB) Offset(offset interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Offset(offset)
}

/**
  Group 和 Having
  rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
  for rows.Next() {
      ...
  }

  rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
  for rows.Next() {
      ...
  }

  type Result struct {
      Date  time.Time
      Total int64
  }
  db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

  Joins
  rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
  for rows.Next() {
      ...
  }

  db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

  多个关联查询
  db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)
*/
func (mysqldb *MysqlDB) Table(name string) *gorm.DB {
	return mysqldb.SlaveNode().Table(name)
}

func (mysqldb *MysqlDB) MasterTable(name string) *gorm.DB {
	return mysqldb.Master.Table(name)
}

/**
  数据库迁移
  drivers.MysqlDb.AutoMigrate(&models.User{})
*/
func (mysqldb *MysqlDB) AutoMigrate(values ...interface{}) *gorm.DB {
	return mysqldb.Master.AutoMigrate(values...)
}

/**
  预加载 | 方向查询并且加载关联数据
  db.Preload("Orders").Preload("Company").Preload("Role").Find(&users)

  带条件的预加载 Order
  db.Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users)
  SELECT * FROM users;
  SELECT * FROM orders WHERE user_id IN (1,2,3,4) AND state NOT IN ('cancelled');
  db.Where("state = ?", "active").Preload("Orders", "state NOT IN (?)", "cancelled").Find(&users)
  SELECT * FROM users WHERE state = 'active';
  SELECT * FROM orders WHERE user_id IN (1,2) AND state NOT IN ('cancelled');
*/
func (mysqldb *MysqlDB) Preload(column string, conditions ...interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Preload(column, conditions...)
}

/**
  Joins 预加载
  Preload 在一个单独查询中加载关联数据。而 Join Preload 会使用 inner join 加载关联数据，例如：
  db.Joins("Company").Joins("Manager").Joins("Account").First(&user, 1)
  注意 Join Preload 适用于一对一的关系，例如： has one, belongs to
*/
func (mysqldb *MysqlDB) Joins(query string, args ...interface{}) *gorm.DB {
	return mysqldb.SlaveNode().Joins(query, args...)
}

func (mysqldb *MysqlDB) Debug() *gorm.DB {
	return mysqldb.SlaveNode().Debug()
}

var SqlClusterDb *MysqlDB

// 加载配置
func loadInit() {

	SqlClusterDb = NewMysqlDB()

	if configs.DBSelectNodeOrCluster() {

		// 加载 集群配置
		initClusterNode()
	} else {
		// get db config
		initNode()
	}
}

func initClusterNode() {

	dbClusterConfig := configs.GetDBClusterConfig()
	dBNodes := NewDBNodes(len(dbClusterConfig))

	for nodeName, node := range dbClusterConfig {
		nodeDb := NewDB(
			node["DB_USER"],
			node["DB_PWD"],
			node["DB_HOST"],
			node["DB_PORT"],
			node["DB_NAME"],
		)

		nodeDb.GetDBUri()
		nodeDb.SetLocal(node["DB_LOC"])
		nodeDb.SetCharset(node["DB_CHARSET"])
		nodeDb.SetMaxOpenConns(node["DB_MAX_OPEN_CONNS"])
		nodeDb.SetParsetTime(node["DB_PARSETIME"])
		nodeDb.SetMaxLifetimeConns(node["DB_MAX_LIFETIME_CONNS"])
		nodeDb.SetMaxIdleConns(node["DB_MAX_IDLE_CONNS"])

		dBNodes.add(nodeName, nodeDb)
	}

	SqlClusterDb.Open(dBNodes)
	MysqlDb = SqlClusterDb.Master
	MysqlDb.SingularTable(true)

}

// 切换db 对象 | 集群如果配置的话，那么优先级比较高
func initNode() {
	dbConfig := configs.GetDbConfig()

	dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s&parseTime=%s&loc=%s",
		dbConfig["DB_USER"],
		dbConfig["DB_PWD"],
		dbConfig["DB_HOST"],
		dbConfig["DB_PORT"],
		dbConfig["DB_NAME"],
		dbConfig["DB_CHARSET"],
		dbConfig["DB_PARSETIME"],
		dbConfig["DB_Loc"],
	)

	// connect and open db connection
	MysqlDb, MysqlDbErr = gorm.Open("mysql", dbDSN)

	if MysqlDbErr != nil {
		panic("database data source name error: " + MysqlDbErr.Error())
	}

	sqlDB := MysqlDb.DB()
	MysqlDb.SingularTable(true)

	// max open connections
	dbMaxOpenConns, _ := strconv.Atoi(dbConfig["DB_MAX_OPEN_CONNS"])
	sqlDB.SetMaxOpenConns(dbMaxOpenConns)

	// max idle connections
	dbMaxIdleConns, _ := strconv.Atoi(dbConfig["DB_MAX_IDLE_CONNS"])
	sqlDB.SetMaxIdleConns(dbMaxIdleConns)

	// max lifetime of connection if <=0 will forever
	dbMaxLifetimeConns, _ := strconv.Atoi(dbConfig["DB_MAX_LIFETIME_CONNS"])
	sqlDB.SetConnMaxLifetime(time.Duration(dbMaxLifetimeConns))

	// check db connection at once avoid connect failed
	// else error will be reported until db first sql operate
	if MysqlDbErr = sqlDB.Ping(); nil != MysqlDbErr {
		panic("database connect failed: " + MysqlDbErr.Error())
	}
}

// 初始化DB对象
func init() {
	// get db config
	loadInit()
}

func MySqlDbClose() {
	if configs.DBSelectNodeOrCluster() {

		// 加载 集群配置
		MysqlDb.Close() // master close
		for _, node := range SqlClusterDb.Slave {
			node.Close()
		}

	} else {
		// 单点关闭
		MysqlDb.Close()
	}
}
